﻿using System;
using System.Collections.Generic;

using System.Web;
using System.Configuration;
using System.Data.Sql;
using System.Data.SqlClient;
using System.ComponentModel;
using System.Web.SessionState;

/// <summary>
/// Summary description for productBv_PV
/// </summary>
namespace Bestlife.ERP.DataAccess
{
    public class product
    {
     public static decimal dis_margin = -1;
     public static decimal FCmargin = -1;
     public static decimal PV = -1;
     public static int pid;
     public static string sub;
     

        public static Boolean update(Bestlife.ERP.Entities.product product,string user)//insert 
        {
            Boolean status = true;
            DateTime date = System.DateTime.Now;

            String connStr = ConfigurationManager.ConnectionStrings["BestlifeDBConnectionString"].ConnectionString;
            Bestlife.ERP.DataAccess.Connection.connect(connStr);//connection string pass to connection class
            string catid = catsub(product.Cat_Name1);
            string subcat = subcatid(product.Sub_ID);
           
           // string sql = "INSERT INTO [Tbl_Product]([Pro_ID],[Code],[Description],[Pro_Name],[Pro_Price],[Ret_Price],[Pur_Price],[Point_Value],[Pro_Type],[Cat_Name],[Sub_Cat_Name],[Percentage],[Business_Value],[Date_Create],[Last_Modify],[Cat_ID],[Sub_Cat])VALUES ('" + product.Pro_ID + "','" + product.Pro_Code + "','" + product.Pro_Des + "','" + product.Pro_Name + "','" + product.Pro_Price + "','" + product.Ret_Price + "','" + product.Pur_Price + "','" + product.Point_Value + "','" + product.Pro_Type + "','" + product.Cat_ID + "','" + product.Sub_ID + "','" + product.Precentage + "','" +product.Bussiness_Value + "','" + date + "','" + date + "','" + catid + "','" + subcat + "')";
            string sql = "INSERT INTO [Tbl_Product]([Pro_ID],[Code],[Sub_Cat],[Brand_Name],[Cat_ID],[Description],[Pro_Name],[Pro_Price],[Ret_Price],[Pur_Price],[Point_Value],[Cat_Name],[Sub_Cat_Name],[Business_Value],[User_Create],[Date_Create],[Status]) VALUES ('"+product.Pro_ID+"','"+product.Pro_Code+"','"+product.Sub_ID+"','"+product.Brand+"','"+product.Cat_Name1+"','"+product.Pro_Des+"','"+product.Pro_Name+"','"+product.Pro_Price+"','"+product.Ret_Price+"','"+product.Pur_Price+"','"+product.Point_Value+"','"+catid+"','"+product.Sub_ID+"','"+product.Bussiness_Value+"','"+user+"','"+date+"','"+product.Status+"')";
            Boolean add = Bestlife.ERP.DataAccess.Connection.AddEditDel(sql);
            if (add)
            {
                status = true;
                return status;
            }
            return status;

        }

        public static Boolean insertproduct(Bestlife.ERP.Entities.product insert,string user)//update
        {
            Boolean status = false;
            DateTime date = System.DateTime.Now;

            String connStr = ConfigurationManager.ConnectionStrings["BestlifeDBConnectionString"].ConnectionString;
            Bestlife.ERP.DataAccess.Connection.connect(connStr);//connection string pass to connection class

            string sql = "UPDATE Tbl_Product SET Code ='" + insert.Pro_Code + "',Description ='" + insert.Pro_Des + "',Pro_Name ='" + insert.Pro_Name + "',Pro_Price ='" + insert.Pro_Price + "',Ret_Price ='" + insert.Ret_Price + "',Pur_Price='" + insert.Pur_Price + "',Point_Value ='" + insert.Point_Value + "',Cat_Name ='" + insert.Cat_ID + "',Sub_Cat_Name ='" + insert.Sub_ID + "',Business_Value='"+insert.Bussiness_Value+"'Last_Modify ='" + date + "' WHERE Pro_ID ='" + insert.Pro_ID + "' ";
            Boolean edit = Bestlife.ERP.DataAccess.Connection.AddEditDel(sql);
            if (edit)
            {
                status = true;

                return status;
            }

            return status;

        }

        public static Boolean image(string[] imgList, string pid)//insert image
        {
            Boolean status = false;
            DateTime date = System.DateTime.Now;

            String connStr = ConfigurationManager.ConnectionStrings["BestlifeDBConnectionString"].ConnectionString;
            Bestlife.ERP.DataAccess.Connection.connect(connStr);//connection string pass to connection class

            string sql = "UPDATE [Tbl_Product] SET [Pro_Image] ='" + imgList[0] + "',[Pro_Image2] ='" + imgList[1] + "',[Pro_Image3] = '" + imgList[2] + "',[Pro_Image4] ='" + imgList[3] + "',[Last_Modify] ='" + date + "' WHERE Pro_ID ='" + pid + "' ";
            Boolean edit = Bestlife.ERP.DataAccess.Connection.AddEditDel(sql);

            if (edit)
            {
                status = true;

                return status;
            }

            return status;

        }

        public static Boolean status()
        {
            SqlDataReader rdR;
            try
            {
                DateTime date = System.DateTime.Now;
                String connStr = ConfigurationManager.ConnectionStrings["BestlifeDBConnectionString"].ConnectionString;
                Bestlife.ERP.DataAccess.Connection.connect(connStr);//connection string pass to connection class

                string Show = "Show";
                String Ssql = "SELECT [Status] FROM [Tbl_Category] where Status='" + Show + "'";

                rdR = Connection.ReadTable(Ssql);

                if (rdR.Read())
                {

                    return true;

                }
                rdR.Close();
            }
            catch (Exception)
            {

            }

            return false;
        }

        public static string contctperson(string supid)
        {
            SqlDataReader rdR;
            try
            {
                DateTime date = System.DateTime.Now;
                String connStr = ConfigurationManager.ConnectionStrings["BestlifeDBConnectionString"].ConnectionString;
                Bestlife.ERP.DataAccess.Connection.connect(connStr);//connection string pass to connection class


                String Ssql = "SELECT [Contact] FROM [Tbl_Supplier] where [Sup_ID]='" + supid + "'";

                rdR = Connection.ReadTable(Ssql);

                if (rdR.Read())
                {
                    string contact = rdR["contact"].ToString();

                    return contact;
                }
                rdR.Close();
            }
            catch (Exception)
            {
            }
            string error = "";


            return error;
        }

        public static decimal pro_price(string name)
        {
            SqlDataReader rdR;
            try
            {
                DateTime date = System.DateTime.Now;
                String connStr = ConfigurationManager.ConnectionStrings["BestlifeDBConnectionString"].ConnectionString;
                Bestlife.ERP.DataAccess.Connection.connect(connStr);//connection string pass to connection class


                String Ssql = "SELECT [Pur_Price] FROM [Tbl_Product] where Pro_Name='" + name + "'";

                rdR = Connection.ReadTable(Ssql);

                if (rdR.Read())
                {
                    decimal price = decimal.Parse(rdR["Pur_Price"].ToString());

                    return price;
                }
                rdR.Close();
            }
            catch (Exception)
            {
            }

            decimal error = 0;

            return error;

        }

        public static string catsub(string cat)
        {
            SqlDataReader rdR;
            try
            {
                DateTime date = System.DateTime.Now;
                String connStr = ConfigurationManager.ConnectionStrings["BestlifeDBConnectionString"].ConnectionString;
                Bestlife.ERP.DataAccess.Connection.connect(connStr);//connection string pass to connection class


                String Ssql = "SELECT [Cat_Name] FROM [Tbl_Category] where [Cat_ID]='" + cat + "'";

                rdR = Connection.ReadTable(Ssql);

                if (rdR.Read())
                {
                    sub = (rdR["Cat_Name"].ToString());
                    rdR.Close();
                    return sub;
                    
                }
                rdR.Close(); 
            }
            catch (Exception)
            {

            }
            
            return sub;
            
        }

        public static string subcatid(string cat)
        {
            SqlDataReader rdR;
            try
            {
                DateTime date = System.DateTime.Now;
                String connStr = ConfigurationManager.ConnectionStrings["BestlifeDBConnectionString"].ConnectionString;
                Bestlife.ERP.DataAccess.Connection.connect(connStr);//connection string pass to connection class


                String Ssql = "SELECT [Sub_Cat_Name] FROM [Tbl_Sub_Category] where [Sub_Cat]='" + cat + "'";

                rdR = Connection.ReadTable(Ssql);

                if (rdR.Read())
                {
                    string sub = (rdR["Sub_Cat_Name"].ToString());
                    rdR.Close();

                    return sub;
                }
                rdR.Close();
            }
            catch (Exception)
            {

            }
            string error = "error";

            return error;
        }

        #region PV,BV,Distrbutor price

        public static decimal setdisprice(decimal cost,decimal mark_price )
        {

            decimal GPratio = grossprofitratio(cost, mark_price);
        decimal i = GPratio / 2;
        decimal Dismargin=Dis_margin(i);
        decimal Disprice=Dis_price(mark_price, Dismargin);
        return Disprice;
        }

        public static decimal setPV(decimal cost, decimal Disprice)
        {
            decimal GPDisprice = GPonDisprice(Disprice, cost);
            decimal FCprofitmargin = FC_profitmargin(GPDisprice);
            decimal NWprofitmargin = NW_profitmargin(GPDisprice, FCprofitmargin);
            decimal PV = Point_value(FCprofitmargin, Disprice);
            return PV;
        }

        public static decimal setBV(decimal cost, decimal Disprice)
        {
           
            decimal GPDisprice = GPonDisprice(Disprice, cost);
            decimal FCprofitmargin = FC_profitmargin(GPDisprice);
            decimal NWprofitmargin = NW_profitmargin(GPDisprice, FCprofitmargin);
            decimal BVratio = Bussiness_ratio(NWprofitmargin);
            decimal PV = Point_value(FCprofitmargin, Disprice);
            decimal BV = Bussiness_Value(BVratio, PV);
            return BV;
        }

        public static decimal grossprofitratio(decimal cost, decimal mark_price)
        {
        
            decimal grosprofit = ((mark_price - cost) / mark_price);
            decimal grosprofitratio = grosprofit * 100;
            return grosprofitratio;
    }

        public static decimal Dis_margin(decimal i)
        {
            
                if (i <= 5)
                {
                    dis_margin = i;
                }
                else if (i > 5)
                {
                    dis_margin = 5;
                }
                return dis_margin;
           
        }

        public static decimal Dis_price(decimal mark_price, decimal Dismargin)
        {
            decimal disprice = mark_price - (mark_price * (Dismargin/100));
            return disprice;
        }

        public static decimal GPonDisprice(decimal Disprice, decimal cost)
        {
            decimal j=(Disprice - cost);
            decimal GPDisprice = ( j/ Disprice) * 100;
           decimal GPonDisprice= Math.Round(GPDisprice, 3);
            return GPonDisprice;
        }

        public static decimal FC_profitmargin(decimal GPDisprice)
        {
          
            if (GPDisprice < 10)
            {
                FCmargin = GPDisprice;
            }
            else if (GPDisprice >= 10 && GPDisprice<60)
            {
                FCmargin = 10;
            }
            else if (GPDisprice > 60)
            {
                FCmargin = (GPDisprice - 60) + 10;
            }
       decimal FCprofitmargin=Math.Round(FCmargin, 3);
            return FCprofitmargin;
        }

        public static decimal NW_profitmargin(decimal GPDisprice, decimal FCprofitmargin)
        {
            decimal NWmargin = GPDisprice - FCprofitmargin;
            decimal NWprofitmargin = Math.Round(NWmargin, 3);
            return NWprofitmargin;
        }

        public static decimal Point_value(decimal FCprofitmargin, decimal Disprice)
        {
           
            if (FCprofitmargin >= 9)
            {
                PV = Disprice / 100;
            }
            else if (FCprofitmargin < 9)
            {
                if (FCprofitmargin >= 0 && FCprofitmargin < 2)
                {
                    PV = Disprice / 500;
                }
                else if (FCprofitmargin >= 2 && FCprofitmargin < 3)
                {
                    PV = Disprice / 450;
                }
                else if (FCprofitmargin >= 3 && FCprofitmargin < 4)
                {
                    PV = Disprice / 400;
                }
                else if (FCprofitmargin >= 4 && FCprofitmargin < 5)
                {
                    PV = Disprice / 350;
                }
                else if (FCprofitmargin >= 5 && FCprofitmargin < 6)
                {
                    PV = Disprice / 300;
                }
                else if (FCprofitmargin >= 6 && FCprofitmargin < 7)
                {
                    PV = Disprice / 250;
                }
                else if (FCprofitmargin >= 7 && FCprofitmargin < 8)
                {
                    PV = Disprice / 200;
                }
                else if (FCprofitmargin >= 8 && FCprofitmargin < 9)
                {
                    PV = Disprice / 150;
                }

            }
            return PV;
        }

        public static decimal Bussiness_ratio(decimal NWprofitmargin)
        {
            decimal ratio = NWprofitmargin * 2;
            decimal BVratio = Math.Round(ratio, 3);
            return BVratio;
        }

        public static decimal Bussiness_Value(decimal BVratio, decimal PV)
        {
            decimal BV =(BVratio/100) * PV;
            return BV;
        }

        public static Boolean insertpvbv(decimal cost, decimal mark_price, string proid, string user)//insert 
        {
            Boolean status = false;
            try
            {
                
                DateTime date = System.DateTime.Now;

                String connStr = ConfigurationManager.ConnectionStrings["BestlifeDBConnectionString"].ConnectionString;
                Bestlife.ERP.DataAccess.Connection.connect(connStr);//connection string pass to connection class


                decimal GPratio = grossprofitratio(cost, mark_price);
                decimal i = GPratio / 2;
                decimal Dismargin = Dis_margin(i);
                decimal Disprice = Dis_price(mark_price, Dismargin);
                decimal GPDisprice = GPonDisprice(Disprice, cost);
                decimal FCprofitmargin = FC_profitmargin(GPDisprice);
                decimal NWprofitmargin = NW_profitmargin(GPDisprice, FCprofitmargin);
                decimal PV = Point_value(FCprofitmargin, Disprice);
                decimal pointvalue = Math.Round((PV), 3);
                decimal BVratio = Bussiness_ratio(NWprofitmargin);

                decimal BV = Bussiness_Value(BVratio, PV);
                decimal bussinessvalue = Math.Round(BV, 3);
                decimal disprice = Math.Round(Disprice, 2);
                string sql = "INSERT INTO [Tbl_PV_BV_Disprice]([Pro_ID],[Dis_Price],[GPonDis_Price],[FC_Profitmargin],[Point_Value],[Network_Profitmargin],[Business_Value],[GP_Ratio],[Dis_Margin],[BV_ratio],[Marked_Price],[Cost]) VALUES('" + proid + "','" + disprice + "','" + GPDisprice + "','" + FCprofitmargin + "','" + pointvalue + "','" + NWprofitmargin + "','" + BV + "','" + GPratio + "','" + Dismargin + "','" + BVratio + "','"+mark_price+"','"+cost+"')";
                // string sql = "INSERT INTO [Tbl_PV_BV_Disprice]([Pro_ID],[GP_Ratio],[Dis_Margin],[Dis_Price],[GPonDis_Price],[FC_Profitmargin],[Network_Profitmargin],[BV_ratio],[Point_Value],[Business_Value],[Cost],[markedprice],[User_Create],[Date_Create]) VALUES('" + proid + "','" + GPratio + "','" + Dismargin + "','" + disprice + "','" + GPDisprice + "','" + FCprofitmargin + "','" + NWprofitmargin + "','" + BVratio + "','" + pointvalue + "','" + bussinessvalue + "','" + cost + "','" + mark_price + "','" + user + "','" + date + "')";

                Boolean add = Bestlife.ERP.DataAccess.Connection.AddEditDel(sql);
                if (add)
                {
                    status = true;
                    return status;
                }
            }
            catch
            {
            }
            return status;

        }

        #endregion

        public static int lastproid()
        {

            try
            {
                SqlDataReader rdR;

                DateTime date = System.DateTime.Now;
                String connStr = ConfigurationManager.ConnectionStrings["BestlifeDBConnectionString"].ConnectionString;
                Bestlife.ERP.DataAccess.Connection.connect(connStr);//connection string pass to connection class

                String Ssql = "SELECT MAX([Pro_ID]) as lastid  FROM [Tbl_Product]";
                // String Ssql = "SELECT [Ret_Price] FROM [Tbl_Product] where [Pro_ID]='" + pro + "'";

                rdR = Connection.ReadTable(Ssql);

                while (rdR.Read())
                {

                    pid = (int)rdR["lastid"] + 1;



                }
                rdR.Close();
              



            }
            catch
            {

            }

            return pid;
        }
    }
}
